/**
 * 功能：
 *@author
 *@date
 */
package com.is.export.control;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Method;
import java.text.ParseException;
import java.util.Collection;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;

import com.is.control.CourseClassControl;
import com.is.control.CourseScoreControl;
import com.is.control.ParamClassControl;
import com.is.control.ParamCourseControl;
import com.is.control.ParamCourseScoreGSControl;
import com.is.control.ParamStuyearControl;
import com.is.control.StudentControl;
import com.is.model.CourseClass;
import com.is.model.CourseScore;
import com.is.model.ParamClass;
import com.is.model.ParamCourseScoreGS;
import com.is.model.ParamStuyear;
import com.is.model.Student;


/**   
 *    
 * 项目名称：InternationalStudent   
 * 类名称：ExportOneClassDetail   
 * 类描述：   导出班级所有科目出勤，班级所有科目成绩
 * 创建人：hiCyoung   
 * 创建时间：2012-12-25 下午5:22:41   
 * 修改人：hiCyoung   
 * 修改时间：2012-12-25 下午5:22:41   

 *    
 */
public class ExportOneClassDetail {
	/**
	 * 导出单个班级某学期所有学习成绩
	 * @param filename
	 * @param classId
	 * @param stuyear
	 * @throws IOException
	 */
	public static void ExportOneClassAllScore(String filename,int classId,int stuyear)throws IOException{
		       List<CourseClass> ccs = CourseClassControl.getAllCourseClassIdByClassIdAndStuyear(classId, stuyear);
		       ParamClass pc =ParamClassControl.getClassById(classId);
		       ParamStuyear ps =ParamStuyearControl.getStuyear(stuyear); 
		       HSSFWorkbook wb = new HSSFWorkbook();
				HSSFSheet sheet = wb.createSheet(ps.getStuyear()+pc.getClassName()+"成绩详细");
				sheet.setColumnWidth(0, 3800); //設置列寬
				
				
				HSSFCellStyle normalStyle = wb.createCellStyle();
				normalStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
				normalStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
				normalStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
				normalStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
				normalStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
				normalStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
				//normalStyle.setWrapText(true);//是否自動換行
				
				//int colIndex = 1;//控制单元格的位置；
				//int colSize = 0;//控制合并单元格的长度
				int startRow =0 ;
				CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0,20);//第一行
				
				sheet.addMergedRegion(cellRangeAddress);
				HSSFRow row = sheet.createRow(startRow++);
				HSSFCell cell = row.createCell(0);// 在索引0的位置创建单元格（左上端）
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);// 定义单元格为字符串类型
				cell.setCellValue(new HSSFRichTextString(ps.getStuyear()+pc.getClassName()+"成绩详细分布"));// 在单元格中输入一些内容
				
				row = sheet.createRow(startRow++);
				cell = row.createCell(0);
				cell.setCellValue("//////");
				cell.setCellStyle(normalStyle);
				
				int startCell=1;
				 for(CourseClass cc:ccs){
					 cellRangeAddress = new CellRangeAddress(1,1,startCell,startCell+3);//合併大項單元格
				     sheet.addMergedRegion(cellRangeAddress);
				     cell = row.createCell(startCell);
				     cell.setCellValue(ParamCourseControl.getCourseById(cc.getCourseId()).getCourseName());
				     cell.setCellStyle(normalStyle);
				     startCell+=4;
				 }
				int tempCell=0;
				row = sheet.createRow(startRow++);
				cell = row.createCell(tempCell++);
				cell.setCellValue("姓名");
				cell.setCellStyle(normalStyle);
				 for(CourseClass cc:ccs){
				     cell = row.createCell(tempCell++);
				     cell.setCellValue("平时分");
				     cell.setCellStyle(normalStyle);
				     
				     cell = row.createCell(tempCell++);
				     cell.setCellValue("期中成绩");
				     cell.setCellStyle(normalStyle);
				      
				     cell = row.createCell(tempCell++);
				     cell.setCellValue("期末成绩");
				     cell.setCellStyle(normalStyle);
				     
				     cell = row.createCell(tempCell++);
				     cell.setCellValue("总分");
				     cell.setCellStyle(normalStyle);
				      
				 }
				 List<Student> stus = StudentControl.getStudentByClassId(classId+"");
				for(Student stu:stus){
				     tempCell=0;
				     row = sheet.createRow(startRow++);
				
				
				     cell = row.createCell(tempCell++);
			         cell.setCellValue(stu.getChineseName());
				     cell.setCellStyle(normalStyle);
				
				     for(CourseClass cc:ccs){
			        	 CourseScore cs = CourseScoreControl.getCSByStuIdAndCourseId(cc.getId(),stu.getId());
				   	      String daily = (cs!=null)?cs.getDailyScore():"0.0";
				   	      String mid = (cs!=null)?cs.getMidtermScore():"0.0";
				   	      String finalS = (cs!=null)?cs.getFinalScore():"0.0";
				   		  String total = (cs!=null)?cs.getTotalScore():"0.0";
				   		 cell = row.createCell(tempCell++);
				         cell.setCellValue(daily);
					     cell.setCellStyle(normalStyle);
					     cell = row.createCell(tempCell++);
				         cell.setCellValue(mid);
					     cell.setCellStyle(normalStyle);
					     cell = row.createCell(tempCell++);
				         cell.setCellValue(finalS);
					     cell.setCellStyle(normalStyle);
					     cell = row.createCell(tempCell++);
				         cell.setCellValue(total);
					     cell.setCellStyle(normalStyle);
				     }
			 	}
				FileOutputStream out = new FileOutputStream(filename);
				wb.write(out);
				out.close();
}
	
	public static void ExportOneClassAllAttendance(String filename,HttpServletRequest req ) throws IOException{
	    LinkedHashMap<Integer,LinkedHashMap<String,String>> stuAtts = (LinkedHashMap<Integer, LinkedHashMap<String, String>>) req.getSession().getAttribute("stuAtts");
	    int classId = Integer.parseInt(req.getSession().getAttribute("exportAttClassId").toString());
	    int stuyear = Integer.parseInt(req.getSession().getAttribute("exportAttrStuyear").toString());
	    ParamClass pc =ParamClassControl.getClassById(classId);
	   // ParamStuyear ps =ParamStuyearControl.getStuyear(stuyear); 
	    String time1 =req.getSession().getAttribute("attTime1")+""; 
	    String time2 =req.getSession().getAttribute("attTime2")+""; 
	    List<CourseClass> ccs = CourseClassControl.getAllCourseClassIdByClassIdAndStuyear(classId, stuyear);
	    HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet(time1+"~"+time2+pc.getClassName()+"出勤统计");
		sheet.setColumnWidth(0, 3800); //設置列寬
		
		
		HSSFCellStyle normalStyle = wb.createCellStyle();
		normalStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		normalStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		normalStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		normalStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		normalStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		normalStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		//normalStyle.setWrapText(true);//是否自動換行
		
		//int colIndex = 1;//控制单元格的位置；
		//int colSize = 0;//控制合并单元格的长度
		int startRow =0 ;
		CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0,20);//第一行
		
		sheet.addMergedRegion(cellRangeAddress);
		HSSFRow row = sheet.createRow(startRow++);
		HSSFCell cell = row.createCell(0);// 在索引0的位置创建单元格（左上端）
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);// 定义单元格为字符串类型
		cell.setCellValue(new HSSFRichTextString(time1+"~"+time2+pc.getClassName()+"出勤统计"));// 在单元格中输入一些内容
		
		row = sheet.createRow(startRow++);
		cell = row.createCell(0);
		cell.setCellValue("//////");
		cell.setCellStyle(normalStyle);
		
		int startCell=1;
		 for(CourseClass cc:ccs){
		     cellRangeAddress = new CellRangeAddress(1,1,startCell,startCell+4);//合併大項單元格
		     sheet.addMergedRegion(cellRangeAddress);
		     cell = row.createCell(startCell);
		     cell.setCellValue(ParamCourseControl.getCourseById(cc.getCourseId()).getCourseName());
		     cell.setCellStyle(normalStyle);
		     startCell+=5;
		 }
		 cellRangeAddress = new CellRangeAddress(1,1,startCell,startCell+2);//合併大項單元格
		 sheet.addMergedRegion(cellRangeAddress);
		 cell = row.createCell(startCell);
		 cell.setCellValue("统计");
		 cell.setCellStyle(normalStyle);
		     
		int tempCell=0;
		row = sheet.createRow(startRow++);
		cell = row.createCell(tempCell++);
		cell.setCellValue("姓名");
		cell.setCellStyle(normalStyle);
		 for(CourseClass cc:ccs){
		     cell = row.createCell(tempCell++);
		     cell.setCellValue("迟到");
		     cell.setCellStyle(normalStyle);
		     
		     cell = row.createCell(tempCell++);
		     cell.setCellValue("缺勤");
		     cell.setCellStyle(normalStyle);
		      
		     cell = row.createCell(tempCell++);
		     cell.setCellValue("病假");
		     cell.setCellStyle(normalStyle);
		     
		     cell = row.createCell(tempCell++);
		     cell.setCellValue("事假");
		     cell.setCellStyle(normalStyle);
		     
		     cell = row.createCell(tempCell++);
		     cell.setCellValue("缺勤率");
		     cell.setCellStyle(normalStyle);
		      
		 }
		     cell = row.createCell(tempCell++);
		     cell.setCellValue("总点名次数");
		     cell.setCellStyle(normalStyle);
		     
		     cell = row.createCell(tempCell++);
		     cell.setCellValue("折合总缺勤数");
		     cell.setCellStyle(normalStyle);
		     
		     cell = row.createCell(tempCell++);
		     cell.setCellValue("折合总缺勤率");
		     cell.setCellStyle(normalStyle);
		     List<Student> stus = StudentControl.getStudentByClassId(classId+"");
			for(Student stu:stus){    
		//for(Map.Entry<Integer, LinkedHashMap<String, String>> mi:stuAtts.entrySet()){
		      // Student student = StudentControl.getStudent(mi.getKey());
		       LinkedHashMap<String, String> stuAtt =stuAtts.get(stu.getId());
		    
			tempCell=0;
		     row = sheet.createRow(startRow++);
		     cell = row.createCell(tempCell++);
	             cell.setCellValue(stu.getChineseName());
		     cell.setCellStyle(normalStyle);
//		     System.out.println("stu:"+stu.getChineseName()+"------");
//		     for(Map.Entry<String, String> stuAt:stuAtt.entrySet()){
//			 System.out.println(stuAt.getKey()+"    "+stuAt.getValue());
//		     }
		     for(CourseClass cc:ccs){
		     //for(Map.Entry<String, String> stuAt:stuAtt.entrySet()){{
		   	      
			    cell = row.createCell(tempCell++);
			     cell.setCellValue(stuAtt.get(cc.getCourseId()+"count2"+stu.getId()));
			     cell.setCellStyle(normalStyle);
			     
			     cell = row.createCell(tempCell++);
			     cell.setCellValue(stuAtt.get(cc.getCourseId()+"count3"+stu.getId()));
			     cell.setCellStyle(normalStyle);
			      
			     cell = row.createCell(tempCell++);
			     cell.setCellValue(stuAtt.get(cc.getCourseId()+"count4"+stu.getId()));
			     cell.setCellStyle(normalStyle);
			     
			     cell = row.createCell(tempCell++);
			     cell.setCellValue(stuAtt.get(cc.getCourseId()+"count5"+stu.getId()));
			     cell.setCellStyle(normalStyle);
			     
			     cell = row.createCell(tempCell++);
			     cell.setCellValue(stuAtt.get(cc.getCourseId()+"absencePer"+stu.getId()));
			     cell.setCellStyle(normalStyle);
		     }
		     cell = row.createCell(tempCell++);
		     cell.setCellValue(stuAtt.get(stu.getId()+"sumTimes"));
		     cell.setCellStyle(normalStyle);
		     
		     cell = row.createCell(tempCell++);
		     cell.setCellValue(stuAtt.get(stu.getId()+"qqTimes"));
		     cell.setCellStyle(normalStyle);
		    
		     cell = row.createCell(tempCell++);
		     cell.setCellValue(stuAtt.get(stu.getId()+"totalAbsencePer"));
		     cell.setCellStyle(normalStyle);
	 	}
		FileOutputStream out = new FileOutputStream(filename);
		wb.write(out);
		out.close();
	}


public static void main(String args[]) throws IOException, ParseException{
	//Export.ExportStatics("D:\\evasystem.xls",2010,1);
	//Export.ExportDetail("D:\\detail.xls",2010,StuyearControl.getStuyearIdByTime(DateControl.Date()));
	//String a ="dddd";
	//System.out.println("\""+a+"\"");
	ExportOneClassAllScore("D:\\ccc.xls", 24, 8);
	System.out.println("OKOKOK");
}


}
